﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using SISCON.Model;
using SISCON.Common;
using log4net;

namespace SISCON.Dao
{
   
    /// <summary>
    /// Clase session la cual se verifica la session de los usuarios del sistema
    /// </summary>
    public class SessionDAO : CommonDAO
    {
    
        /// <summary>
        /// Crea una session para usarse en el sistema
        /// </summary>
        /// <param name="user">Usuario del sistema</param>
        /// <returns>La session generada por el sistema</returns>
        public string CreateSession(User user)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                SqlCommand sqlCommand = new SqlCommand("sp_insert_session", conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add(new SqlParameter("@user_id", user.UserId));

                SqlParameter sessionUUIDParameter = new SqlParameter("@session_uuid", DbType.String);
                sessionUUIDParameter.SqlDbType = SqlDbType.VarChar;
                sessionUUIDParameter.Size = 100;
                sessionUUIDParameter.Direction = ParameterDirection.Output;

                sqlCommand.Parameters.Add(sessionUUIDParameter);

                conn.Open();
                sqlCommand.ExecuteNonQuery();

                return sqlCommand.Parameters["@session_uuid"].Value.ToString();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        
        /// <summary>
        /// Verifica si la session actual del usuario esta activa en el sistema
        /// </summary>
        public void SessionVerif()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                SqlCommand sqlCommand = new SqlCommand("UPDATE sis_session SET is_active = '0' WHERE DATEDIFF(minute, last_update_date, SYSDATETIME()) >= 30", conn);
                conn.Open();
                reader = sqlCommand.ExecuteReader();
            }
            catch (Exception exp)
            {
                throw exp;
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        
        /// <summary>
        /// Metodo regresa la lista de todas las sessiones del sistema 
        /// </summary>
        /// <param name="sessionUUID">la session del usuario</param>
        /// <param name="proyectName">Nombre del proyecto</param>
        /// <returns>Una lista de sessiones</returns>
        public List<Session> GetSessions(string sessionUUID, string proyectName)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            List<Session> sessions = new List<Session>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                SqlCommand sqlCommand = new SqlCommand("select sis_session.user_id, sis_session.is_active, sis_user.login_name, " +
                    " sis_session.last_update_date, sis_user.creation_date, sis_session.session_uuid" +
                    " from sis_session " +
                    " LEFT OUTER JOIN sis_user on (sis_session.user_id = sis_user.user_id) " +
                    " where (sis_session.is_active=1) " +
                    " AND (sis_session.session_uuid not like '" + sessionUUID + "')", conn);
                sqlCommand.Parameters.Add(getNewParameter("@proyectName", SqlDbType.NVarChar, 30, proyectName));
                conn.Open();
                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {

                    Session session = new Session();
                    session.UserId = reader["user_id"].ToString();
                    session.Username = reader["login_name"].ToString();
                    session.LastUpdateDate = (DateTime)reader["last_update_date"];
                    session.SessionUuid = reader["session_uuid"].ToString();
                    session.IsActive = (bool)reader["is_active"];

                    sessions.Add(session);
                }

            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();

                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();

                }
            }

            return sessions;
        }

        /// <summary>
        /// Obtiene la session de un usuario
        /// </summary>
        /// <param name="sessionUUID">Session de un usuario</param>
        /// <returns>Session</returns>
        public Session GetSession(string sessionUUID)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            Session session = null;

            try
            {
                conn = new SqlConnection(_ConnStr);
                SqlCommand sqlCommand = new SqlCommand("SELECT * FROM sis_session WHERE session_uuid =  '" + sessionUUID + "'", conn);
                conn.Open();
                reader = sqlCommand.ExecuteReader();

                if (reader.Read())
                {
                    session = new Session();
                    session.SessionId = getReaderLong(reader, "session_id");
                    session.SessionUuid = getReaderString(reader, "session_uuid");
                    session.LastUpdateDate = getReaderDateTime(reader, "last_update_date");
                    session.UserId = getReaderString(reader, "session_uuid");
                    session.IsActive = getReaderBool(reader, "is_active");
                }
                else
                {
                    throw new SISCONException("Sesión inválida");
                }

            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();

                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();

                }
            }

            return session;
        }
        
        /// <summary>
        /// actualiza la session actual del usuario
        /// </summary>
        /// <param name="sessionUUID">Session del usuario</param>
        public void UpdateSession(string sessionUUID)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                SqlTransaction trx = conn.BeginTransaction();
                SqlCommand sqlCommand = new SqlCommand("UPDATE sis_session SET last_update_date = SYSDATETIME() WHERE session_uuid = @guid", conn, trx);

                System.Data.SqlTypes.SqlGuid guid;
                try
                {
                    guid = System.Data.SqlTypes.SqlGuid.Parse(sessionUUID);
                }
                catch (Exception e)
                {
                    throw new SISCONException("No cuenta con los permisos necesarios para la operación solicitada");
                }

                sqlCommand.Parameters.Add(getNewParameter("@guid", SqlDbType.UniqueIdentifier, 0, guid));
                sqlCommand.Prepare();
                sqlCommand.ExecuteNonQuery();

                trx.Commit();
            }
            catch (Exception exp)
            {
                throw exp;
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}